﻿CREATE PROCEDURE [dbo].[sp_Import_Agent]

AS
BEGIN
	truncate table [dbo].[Agents]
	
	INSERT INTO [dbo].[Agents]
           ([Name]
           ,[Latitude]
           ,[Longitude]
           ,[MdNumber]
           ,[ActiveContacts]
           ,[ActiveAppointMade]
           ,[ActiveAppointKept]
           ,[ActivePresentation]
           ,[ActiveAppointSale]
           ,[LapsedContacts]
           ,[LapsedAppointMade]
           ,[LapsedAppointKept]
           ,[LapsedPresentation]
           ,[LapsedAppointSale]
           ,[NeighborContacts]
           ,[NeighborAppointMade]
           ,[NeighborAppointKept]
           ,[NeighborPresentation]
           ,[NeighborAppointSale]
           ,[BusinessContacts]
           ,[BusinessAppointMade]
           ,[BusinessAppointKept]
           ,[BusinessPresentation]
           ,[BusinessAppointSale]
           ,[RefContacts]
           ,[RefAppointMade]
           ,[RefAppointKept]
           ,[RefPresentation]
           ,[RefAppointSale]
           ,[TSystemContacts]
           ,[TSystemAppointMade]
           ,[TSystemAppointKept]
           ,[TSystemPresentation]
           ,[TSystemAppointSale]
           ,[TargetMarketContacts]
           ,[TargetMarketAppointMade]
           ,[TargetMarketAppointKept]
           ,[TargetMarketPresentation]
           ,[TargetMarketAppointSale]
           ,[FieldContacts]
           ,[FieldAppointMade]
           ,[FieldAppointKept]
           ,[FieldPresentation]
           ,[FieldAppointSale]
           ,[AvgAPVperApp]
           ,[AgentCode]
           ,[Territory])
select 
ar.FIRSTNAME + ' ' + ar.LASTNAME,
ar.Latitude,
ar.Longitude,
a.MdNumber,
0.9
,0.5
,0.75
,0.6
,0.25
,0.6
,0.4
,0.6
,0.5
,0.25
,0.9
,0.5
,0.6
,0.33
,0.25
,0.7
,0.6
,0.5
,0.33
,0.25
,0.6
,0.5
,0.6
,0.33
,0.25
,0.9
,0.5
,0.6
,0.33
,0.25
,0.75
,0.5
,0.6
,0.33
,0.25
,0.9
,0.5
,0.75
,0.6
,0.25
,475
,ar.AGTCODE
,ar.TERRITORY
from dbo.AlphaRoster ar
inner join (SELECT MdNumber, Division, Market FROM ZipInfo GROUP BY MdNumber, Division, Market) a
on ar.REGION = a.Division and ar.market = a.Market

update Agents set Name = Name + Territory
	where Name in 
	(select Name from Agents
	group by Name having COUNT(*) > 1)
END
